﻿Imports System.Text

Public Class CommonCreateListenDb

    Public Shared Function DbTableToListenTable(ByVal dt As DataTable, ByVal tableName As String, ByVal prefix As String) As String

        Dim sb As New StringBuilder
        sb.AppendLine("CREATE TABLE [dbo].[" + prefix + "_" + tableName + "](")
        sb.AppendLine(" [" + prefix + "_Key] [decimal](20, 6) NULL")
        sb.AppendLine(",[" + prefix + "_Type] [decimal](1, 0) NULL")
        sb.AppendLine(",[" + prefix + "_UpdateTimer] [decimal](20, 6) NULL")
        sb.AppendLine(",[" + prefix + "_UpdateId] [int] NULL")
        For rowColumn As Integer = 0 To dt.Rows.Count - 1
            sb.AppendLine("," & ColumnUtil.列(dt.Rows(rowColumn)))
        Next

        sb.AppendLine(")")

        Return sb.ToString

    End Function

    Public Shared Function Create_Table_XSC_Count(ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("CREATE TABLE [dbo].[" + prefix + "_Count](")
        sb.AppendLine("	[ID] [int] NOT NULL,")
        sb.AppendLine("	[Type] [decimal](1, 0) NULL,")
        sb.AppendLine(" [Key_Timer] [decimal](20, 6) NULL,")
        sb.AppendLine(" CONSTRAINT [PK_" + prefix + "_Count] PRIMARY KEY CLUSTERED ")
        sb.AppendLine("(")
        sb.AppendLine("	[ID] ASC")
        sb.AppendLine("))")

        Return sb.ToString

    End Function

    Public Shared Function Create_Table_XSC_Control(ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("CREATE TABLE [dbo].[" + prefix + "_Control](")
        sb.AppendLine("	[Kubun] [char](3) NULL,")
        sb.AppendLine("	[Prefix] [nvarchar](20) NULL,")
        sb.AppendLine(" [ID] [int] NULL,")
        sb.AppendLine(" [Status] [int] NULL)")
        Return sb.ToString

    End Function

    Public Shared Function Create_Table_T_Wys_Table(ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("CREATE TABLE [dbo].[" + prefix + "_Key](")
        sb.AppendLine("	[Key_Timer] [decimal](20, 6) NOT NULL,")
        sb.AppendLine("	[Descripion] [nvarchar](100) NULL,")
        sb.AppendLine("PRIMARY KEY CLUSTERED ")
        sb.AppendLine("(")
        sb.AppendLine("	[Key_Timer] ASC")
        sb.AppendLine("))")

        Return sb.ToString

    End Function

    Public Shared Function Create_Table_T_Wys_Key(ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("CREATE TABLE [dbo].[" + prefix + "_Table](")
        sb.AppendLine("	[ID] [int]  NOT NULL,")
        sb.AppendLine("	[Key_Timer] [decimal](20, 6) NOT NULL,")
        sb.AppendLine("	[U_TableName] [nvarchar](255) NOT NULL,")
        sb.AppendLine("	[Table_UpdateTime] [decimal](20, 6) NOT NULL,")
        sb.AppendLine(" [TypeName] [nvarchar](10)  NULL,")
        sb.AppendLine("	 CONSTRAINT [PK_" + prefix + "_Table] PRIMARY KEY CLUSTERED ")
        sb.AppendLine("	(")
        sb.AppendLine("		[ID] ASC")
        sb.AppendLine("))")

        Return sb.ToString

    End Function

    Public Shared Function Create_P_T_Wys_Table(ByVal prefix As String) As String

        Dim sb As New StringBuilder

        sb.AppendLine("CREATE PROCEDURE [dbo].[" + prefix + "_P_Table]")
        sb.AppendLine(" (")
        sb.AppendLine("    @Key_Time       decimal(20, 6),")
        sb.AppendLine("    @TableName      nvarchar(255),")
        sb.AppendLine("    @Type           decimal(1,0),")
        sb.AppendLine("    @ID             int")
        sb.AppendLine("  )")

        sb.AppendLine("  AS")
        sb.AppendLine("  BEGIN")
        sb.AppendLine("	 SET NOCOUNT ON")
        sb.AppendLine("	 DECLARE @ERR           int,")
        sb.AppendLine("            @ROWCNT        int")

        sb.AppendLine("	 DECLARE @TypeName nvarchar(10)	")
        sb.AppendLine("  if @Type=1	 ")
        sb.AppendLine("		set @TypeName=N'添加'")
        sb.AppendLine("	 else if @Type=2")
        sb.AppendLine("	    set @TypeName=N'修改前'")
        sb.AppendLine("	 else if @Type=3")
        sb.AppendLine("	    set @TypeName=N'修改后'")
        sb.AppendLine("	 else if @Type=4")
        sb.AppendLine("		set @TypeName=N'删除'")
        sb.AppendLine("	 else")
        sb.AppendLine("		set @TypeName=''")

        sb.AppendLine("	 DECLARE @Table_UpdateTime decimal(20,6)")
        sb.AppendLine("  SELECT @Table_UpdateTime=CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(40),CURRENT_TIMESTAMP,121),'-',''),':',''),' ','') AS DECIMAL(20,6)) ")

        sb.AppendLine("    BEGIN")

        sb.AppendLine("       INSERT INTO " + prefix + "_Count")
        sb.AppendLine("              (")
        sb.AppendLine("              ID")
        sb.AppendLine("              ,Type")
        sb.AppendLine("              ,Key_Timer")
        sb.AppendLine("              )")
        sb.AppendLine("               VALUES")
        sb.AppendLine("               (")
        sb.AppendLine("               @ID ")
        sb.AppendLine("               ,@Type  ")
        sb.AppendLine("               ,@Key_Time)")
        sb.AppendLine("       ")

        sb.AppendLine("       INSERT INTO " + prefix + "_Table")
        sb.AppendLine("       (Key_Timer")
        sb.AppendLine("       ,U_TableName ")
        sb.AppendLine("       ,Table_UpdateTime")
        sb.AppendLine("       ,TypeName")
        sb.AppendLine("       ,ID")
        sb.AppendLine("       )")
        sb.AppendLine("       VALUES")
        sb.AppendLine("       (@Key_Time")
        sb.AppendLine("       ,@TableName")
        sb.AppendLine("       ,@Table_UpdateTime")
        sb.AppendLine("       ,@TypeName")
        sb.AppendLine("       ,@ID")
        sb.AppendLine("       )")
        sb.AppendLine("    END")

        sb.AppendLine("  END")

        Return sb.ToString

    End Function

End Class